package com.penta.services;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.NumberUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.penta.entity.HeadInfoVO;
import com.penta.helper.CellStyleBuilder;
import com.penta.helper.FontBuilder;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFDrawing;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFPicture;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * 模板1输出服务类，主要用于将内容写入模板文档
 */
public class ModuleOneService extends ModuleService {

    /**
     * writer1的当前索引
     */
    private int writer1CurrIndex=11;

    /**
     * writer2的当前索引
     */
    private int writer2CurrIndex=10;

    public ModuleOneService(ExcelWriter writer1, ExcelWriter writer2, List<List<Object>> dataList, List<byte[]> pictureDataList) {
        this.writer1 = writer1;
        this.writer2 = writer2;
        this.dataList = dataList;
        this.pictureDataList = pictureDataList;
        this.writer1Eval = writer1.getWorkbook().getCreationHelper().createFormulaEvaluator();    // 计算句柄
        this.writer2Eval = writer2.getWorkbook().getCreationHelper().createFormulaEvaluator();    // 计算句柄
    }


    /**
     * 开始执行输出
     */
    @Override
    public void execOutput() {
        /**
         * 取出表头信息
         */
        HeadInfoVO headInfoVO = getHeadInfoByDataList();
        outputHead(headInfoVO);      // 转出表头

        /**
         * 取出产品列表(物料列表)
         * 返回的物料列表，每个都是一个Map
         *
         */
        List<Map<String, Object>> materialList = getMaterialListFromDataList(dataList,pictureDataList);

        // 输出生成1的物料列表信息
        writeMaterialListForOutput1(materialList,headInfoVO);

        // 输出生成2的物料列表信息
        writeMaterialListForOutput2(materialList,headInfoVO);

    }

    /**
     * 输出物料列表-生成2  带图片的
     * @param materialList
     * @param headInfoVO
     */
    private void writeMaterialListForOutput2(List<Map<String, Object>> materialList, HeadInfoVO headInfoVO) {

        /**
         * 如果物料列表为空，则不再写入，直接返回
         */
        if(CollectionUtil.isEmpty(materialList)) {
            return;
        }

        writer2.passRows(writer2CurrIndex);     // 先跳过一定的行，再进行写入

        List<String> collectTtlQtyList = new ArrayList<>();
        List<String> collectTtlCtnsList = new ArrayList<>();
        List<String> collectTtlCbmList = new ArrayList<>();
        List<String> collectTtlNwList = new ArrayList<>();
        List<String> collectTtlGwList = new ArrayList<>();

        /**
         * 遍历并输出
         */
        materialList.stream().forEach(map->{

            String supplierName = String.valueOf(map.get("supplierName"));          // 取出供应商名称
            boolean colorWithPhoto = Boolean.valueOf(String.valueOf(map.get("colorWithPhoto")));    // 颜色是否带图片
            String type = headInfoVO.getType();                                     // 集装箱的信息
            List<Object> supplierDataList = (List<Object>)map.get("dataList");      // 取出该供应商的物料列表
            int startIndex = writer2CurrIndex;      // 开始的索引
            for(Object obj:supplierDataList) {    // 遍历列表并输出

                ArrayList<Object> objects = (ArrayList<Object>)obj;       // 取出数据

                String no = ObjectUtil.isNotEmpty(objects.get(0))?String.valueOf(objects.get(0)):null;                // 序号
                String itemNo = ObjectUtil.isNotEmpty(objects.get(2))?String.valueOf(objects.get(2)):null;            // itemNo
                String description = ObjectUtil.isNotEmpty(objects.get(3))?String.valueOf(objects.get(3)):null;            // 描述，可能带逗号，我们只需要取逗号前的内容
                if(StrUtil.isNotBlank(description)) {
                    description = description.split("[,|，]")[0];    // 截取逗号前的内容
                }
                String color = ObjectUtil.isNotEmpty(objects.get(4))?String.valueOf(objects.get(4)):null;            // 颜色
                String material = ObjectUtil.isNotEmpty(objects.get(5))?String.valueOf(objects.get(5)):null;      // material
                String itemSize = ObjectUtil.isNotEmpty(objects.get(6))?String.valueOf(objects.get(6)):null;      // itemSize
                int totalQty = 0;
                if(ObjectUtil.isNotEmpty(objects.get(18))) {
                    try {
                        totalQty = Integer.valueOf(String.valueOf(objects.get(18)));      // 总数量
                    } catch (Exception e) {
                        // e.printStackTrace();
                    }
                }
                Double totalCBM = 0d;
                if(ObjectUtil.isNotEmpty(objects.get(11))) {
                    try {
                        totalCBM = Double.valueOf(String.valueOf(objects.get(11)));    // totalCBM
                    } catch (Exception e) {
                        //e.printStackTrace();
                    }
                }
                Double totalNW = 0d;
                if(ObjectUtil.isNotEmpty(objects.get(16))) {
                    try {
                        totalNW = Double.valueOf(String.valueOf(objects.get(16)));    // totalNW
                    } catch (Exception e) {
                        //e.printStackTrace();
                    }
                }
                Double totalGW = 0d;
                if(ObjectUtil.isNotEmpty(objects.get(17))) {
                    try {
                        totalGW = Double.valueOf(String.valueOf(objects.get(17)));    // totalGW
                    } catch (Exception e) {
                        //e.printStackTrace();
                    }
                }


                if(colorWithPhoto) {     // 颜色是否带图片
                    try {
                        Object colorPhotoObj = objects.get(objects.size()-1);      // 颜色图片
                        Object itemPhotoObj = objects.get(objects.size()-2);       // 产品图片
                        byte[] itemPhotoByteArr = null;         // 产品图片字节数组
                        byte[] colorPhotoByteArr = null;        // 颜色图片字节数组
                        if(ObjectUtil.isNotEmpty(itemPhotoObj)) {
                            itemPhotoByteArr = (byte[])itemPhotoObj;
                        }
                        if(ObjectUtil.isNotEmpty(colorPhotoObj)) {
                            colorPhotoByteArr = (byte[])colorPhotoObj;
                        }
                        writerPicture(writer2,itemPhotoByteArr,colorPhotoByteArr,writer2CurrIndex);    // 写两个图片
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                } else {                    // 颜色不带图片时
                    try {
                        Object itemPhotoObj = objects.get(objects.size()-1);       // 产品图片
                        if(ObjectUtil.isNotEmpty(itemPhotoObj)) {
                            byte[] itemPhotoByteArr = (byte[])itemPhotoObj;
                            writerPicture(writer2,itemPhotoByteArr,null,writer2CurrIndex);     // 写单个图片
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }

                List<Object> rowData = CollUtil.newArrayList(no,null,itemNo, description, color,material,itemSize,totalQty,totalQty,totalCBM,totalNW,totalGW);
                writeRowDataForOutput2(writer2,rowData,writer2CurrIndex);
                writer2CurrIndex++;

            }
            int endIndex = writer2CurrIndex;    // 结束的索引

            // 写入汇总行
            String ttlQtyFormula = "SUM(H" + (startIndex + 1) + ":H" + endIndex + ")";          // 小计总数量公式
            String ttlCtnsFormula = "SUM(I" + (startIndex + 1) + ":I" + endIndex + ")";         // 小计Ctns总量
            String ttlCbmFormula = "SUM(J" + (startIndex + 1) + ":J" + endIndex + ")";          // ttlCbmFormula
            String ttlNWFormula = "SUM(K" + (startIndex + 1) + ":K" + endIndex + ")";           // ttlNWFormula
            String ttlGWFormula = "SUM(L" + (startIndex + 1) + ":L" + endIndex + ")";           // ttlGWFormula

            collectTtlQtyList.add("H" + (endIndex + 1));
            collectTtlCtnsList.add("I" + (endIndex + 1));
            collectTtlCbmList.add("J" + (endIndex + 1));
            collectTtlNwList.add("K" + (endIndex + 1));
            collectTtlGwList.add("L" + (endIndex + 1));

            writeTotalForOutput2(writer2,supplierName,type,ttlQtyFormula,ttlCtnsFormula,ttlCbmFormula,ttlNWFormula,ttlGWFormula);

        });

        /**
         * 输出全部的汇总行List<Object> totalRowData =
         */
        writerCollectForOutput2(writer2,headInfoVO,collectTtlQtyList,collectTtlCtnsList,collectTtlCbmList,collectTtlNwList,collectTtlGwList);
    }

    /**
     * 输出全部的汇总
     * @param writer
     * @param headInfoVO
     * @param collectTtlQtyList
     * @param collectTtlCtnsList
     * @param collectTtlCbmList
     * @param collectTtlNwList
     * @param collectTtlGwList
     */
    public void writerCollectForOutput2(ExcelWriter writer,HeadInfoVO headInfoVO,List<String> collectTtlQtyList,List<String> collectTtlCtnsList,List<String> collectTtlCbmList,List<String> collectTtlNwList,List<String> collectTtlGwList) {

        writer2.merge(writer2CurrIndex,writer2CurrIndex,0,6,headInfoVO.getSupplier() + "-" + headInfoVO.getType() + " TOTAL ", false);

        List<Object> collectRowData = CollUtil.newArrayList(headInfoVO.getSupplier() + "-" + headInfoVO.getType() + " TOTAL ","","","","","","","","","","","");
        //writer2.writeRow(collectRowData);
        writer2.passCurrentRow();

        // 设置格式
        Row collectRow = writer2.getSheet().getRow(writer2CurrIndex);
        collectRow.setHeightInPoints(30);

        Font font = new FontBuilder(writer2).setFontName("Arial").setFontHeight(9).setBold(true).build();
        for(int i=0;i<collectRowData.size();i++) {
            Cell currCell = collectRow.getCell(i);
            if(currCell==null) {
                currCell = collectRow.createCell(i);
            }
            if(i==0) {     // 第一列
                currCell.setCellStyle(new CellStyleBuilder(writer2).setCellBorder(0,0,0,22).setHorizontalAlignment(HorizontalAlignment.CENTER).setVerticalAlignment(VerticalAlignment.CENTER).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(font).build());    // 上左下边框
            } else if(i == collectRowData.size()-1) {    // 最后一列
                currCell.setCellStyle(new CellStyleBuilder(writer2).setCellBorder(0,22,0,0).setHorizontalAlignment(HorizontalAlignment.CENTER).setVerticalAlignment(VerticalAlignment.CENTER).setDataFormat(2).setFont(font).build());    // 四边都加边框
            } else if(i==9){
                currCell.setCellStyle(new CellStyleBuilder(writer2).setDataFormat(3).setCellBorder(0,22,0,1).setHorizontalAlignment(HorizontalAlignment.CENTER).setVerticalAlignment(VerticalAlignment.CENTER).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(font).build());    // 无边框
            }else if(i==10){
                currCell.setCellStyle(new CellStyleBuilder(writer2).setDataFormat(2).setCellBorder(0,22,0,1).setHorizontalAlignment(HorizontalAlignment.CENTER).setVerticalAlignment(VerticalAlignment.CENTER).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(font).build());    // 无边框
            } else {
                currCell.setCellStyle(new CellStyleBuilder(writer2).setCellBorder(0,22,0,22).setHorizontalAlignment(HorizontalAlignment.CENTER).setVerticalAlignment(VerticalAlignment.CENTER).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(font).build());    // 无边框
            }

            /**
             * 设置公式
             */
            if(i==7) {
                currCell.setCellFormula(collectTtlQtyList.stream().collect(Collectors.joining(" + ")));
                writer2Eval.evaluateFormulaCell(currCell);
            } else if(i==8) {
                currCell.setCellFormula(collectTtlCtnsList.stream().collect(Collectors.joining(" + ")));
                writer2Eval.evaluateFormulaCell(currCell);
            }else if(i==9) {
                currCell.setCellFormula(collectTtlCbmList.stream().collect(Collectors.joining(" + ")));
                writer2Eval.evaluateFormulaCell(currCell);
            }else if(i==10) {
                currCell.setCellFormula(collectTtlNwList.stream().collect(Collectors.joining(" + ")));
                writer2Eval.evaluateFormulaCell(currCell);
            }else if(i==11) {
                currCell.setCellFormula(collectTtlGwList.stream().collect(Collectors.joining(" + ")));
                writer2Eval.evaluateFormulaCell(currCell);
            }

        }

    }

    /**
     * 写入图片
     *
     * @param writer
     * @param itemPhotoByteArr
     * @param colorPhotoByteArr
     * @param currIndex
     */
    public void writerPicture(ExcelWriter writer,byte[] itemPhotoByteArr,byte[] colorPhotoByteArr,int currIndex) {
        // 如果产品图片、颜色图片都为空，则不再往下写图片
        boolean itemPhotoExist = ObjectUtil.isNotEmpty(itemPhotoByteArr) && itemPhotoByteArr.length > 0;     // 是否存在产品图片
        boolean colorPhotoExist = ObjectUtil.isNotEmpty(colorPhotoByteArr) && colorPhotoByteArr.length > 0;  // 是否存在颜色图片
        // 如果两个图片都为空时，直接返回
        if(!itemPhotoExist && !colorPhotoExist) {
            return;
        }

        Workbook workbook = writer.getWorkbook();
        Sheet sheet = workbook.getSheetAt(0);

        //Drawing<?> drawing = sheet.createDrawingPatriarch();

        XSSFDrawing drawing = (XSSFDrawing)sheet.createDrawingPatriarch();

        /**
         * 如果两个图片都不为空，则输出两个图片
         */
        if(itemPhotoExist && colorPhotoExist) {
            int itemPhotoIdx = workbook.addPicture(itemPhotoByteArr, Workbook.PICTURE_TYPE_PNG);    // 添加一个产品图片索引
            int colorPhotoIdx = workbook.addPicture(colorPhotoByteArr, Workbook.PICTURE_TYPE_PNG);    // 添加一个颜色图片索引

            /**
             * 画入图片
             */

            // XSSFClientAnchor itemPhotoAnchor = drawing.createAnchor(150, 150, 300, 300, 1, currIndex, 2, currIndex + 1);
            ClientAnchor itemPhotoAnchor = drawing.createAnchor(50, 50, 400, 250, 1, currIndex, 2, currIndex + 1);

            drawing.createPicture(itemPhotoAnchor, itemPhotoIdx);   // 写入产品图片
            ClientAnchor colorPhotoAnchor = drawing.createAnchor(150, 150, 300, 300, 12, currIndex, 13, currIndex + 1);
            drawing.createPicture(colorPhotoAnchor, colorPhotoIdx);  // 写入产品图片
        } else {     // 只有一个存在

            byte[] photoBytes = null;
            if(itemPhotoExist) {
                photoBytes = itemPhotoByteArr;
            } else {
                photoBytes = colorPhotoByteArr;
            }

            int photoIdx = workbook.addPicture(photoBytes, Workbook.PICTURE_TYPE_PNG);    // 添加一个图片索引
            ClientAnchor photoAnchor = drawing.createAnchor(500, 50, 950, 200, 1, currIndex, 2, currIndex + 1);
            drawing.createPicture(photoAnchor, photoIdx);  // 写入产品图片
        }


    }

    /**
     * 写入汇总-生成2
     * @param writer2
     * @param supplierName
     * @param type
     * @param supplierName
     * @param ttlQtyFormula
     * @param ttlCtnsFormula
     * @param ttlCbmFormula
     * @param ttlNWFormula
     * @param ttlGWFormula
     */
    private void writeTotalForOutput2(ExcelWriter writer2,String supplierName,String type, String ttlQtyFormula, String ttlCtnsFormula, String ttlCbmFormula, String ttlNWFormula, String ttlGWFormula) {

        writer2.merge(writer2CurrIndex,writer2CurrIndex,0,6,supplierName + " TOTAL " + type,false);

        List<Object> totalRowData = CollUtil.newArrayList(supplierName + " TOTAL " + type,"","","","","","","","","","","");
        //writer2.writeRow(totalRowData);*/
        writer2.passCurrentRow();

        // 设置格式
        Row totalRow = writer2.getSheet().getRow(writer2CurrIndex);
        totalRow.setHeightInPoints(30);
        Font font = new FontBuilder(writer2).setFontName("Arial").setFontHeight(9).setBold(true).build();
        for(int i=0;i<totalRowData.size();i++) {
            Cell currCell = totalRow.getCell(i);
            if(currCell==null) {
                currCell = totalRow.createCell(i);
            }
            if(i==0) {     // 第一列
                currCell.setCellStyle(new CellStyleBuilder(writer2).setCellBorder(0,0,0,22).setHorizontalAlignment(HorizontalAlignment.CENTER).setVerticalAlignment(VerticalAlignment.CENTER).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(font).build());    // 上左下边框
            } else if(i == totalRowData.size()-1) {    // 最后一列
                currCell.setCellStyle(new CellStyleBuilder(writer2).setCellBorder(0,22,0,0).setHorizontalAlignment(HorizontalAlignment.CENTER).setVerticalAlignment(VerticalAlignment.CENTER).setDataFormat(2).setFont(font).build());    // 四边都加边框
            } else if(i==9){
                currCell.setCellStyle(new CellStyleBuilder(writer2).setDataFormat(3).setCellBorder(0,22,0,1).setHorizontalAlignment(HorizontalAlignment.CENTER).setVerticalAlignment(VerticalAlignment.CENTER).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(font).build());    // 无边框
            }else if(i==10){
                currCell.setCellStyle(new CellStyleBuilder(writer2).setDataFormat(2).setCellBorder(0,22,0,1).setHorizontalAlignment(HorizontalAlignment.CENTER).setVerticalAlignment(VerticalAlignment.CENTER).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(font).build());    // 无边框
            } else {
                currCell.setCellStyle(new CellStyleBuilder(writer2).setCellBorder(0,22,0,22).setHorizontalAlignment(HorizontalAlignment.CENTER).setVerticalAlignment(VerticalAlignment.CENTER).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(font).build());    // 无边框
            }

            /**
             * 设置公式
             */
            if(i==7) {
                currCell.setCellFormula(ttlQtyFormula);
                writer2Eval.evaluateFormulaCell(currCell);
            } else if(i==8) {
                currCell.setCellFormula(ttlCtnsFormula);
                writer2Eval.evaluateFormulaCell(currCell);
            }else if(i==9) {
                currCell.setCellFormula(ttlCbmFormula);
                writer2Eval.evaluateFormulaCell(currCell);
            }else if(i==10) {
                currCell.setCellFormula(ttlNWFormula);
                writer2Eval.evaluateFormulaCell(currCell);
            }else if(i==11) {
                currCell.setCellFormula(ttlGWFormula);
                writer2Eval.evaluateFormulaCell(currCell);
            }

        }

        writer2CurrIndex++;

        /**
         * 再写入一行空行
         */
        // 设置格式
        Row currRow = writer2.getSheet().getRow(writer2CurrIndex);   // 空行行
        List<String> blankRow = CollUtil.newArrayList("","","","","","","","","","","","");
        // 再写入空行
        writerBlankRow(writer2,blankRow,writer2CurrIndex,"3");     // 写入空行，不设置下边框

        // 设置第一个单元格的左边框为黑色
        // 设置最后一个单元格的右边框为黑色
        if(currRow==null) {
            currRow = writer2.getSheet().createRow(writer2CurrIndex);
        }
        for(int i=0;i<blankRow.size();i++) {
            Cell currCell = currRow.getCell(i);
            if(currCell==null) {
                currCell = currRow.createCell(i);
            }
            if(i==0) {
                currCell.setCellStyle(new CellStyleBuilder(writer2).setCellBorder(0,0,0,1).build());
            } else if(i == blankRow.size()-1) {
                currCell.setCellStyle(new CellStyleBuilder(writer2).setCellBorder(0,1,0,0).build());
            }else {
                currCell.setCellStyle(new CellStyleBuilder(writer2).setCellBorder(0,1,0,1).build());
            }
        }


        writer2CurrIndex++;

    }

    /**
     * 输出物料列表-生成1
     * @param materialList
     */
    private void writeMaterialListForOutput1(List<Map<String, Object>> materialList,HeadInfoVO headInfoVO) {
        /**
         * 如果物料列表为空，则不再写入，直接返回
         */
        if(CollectionUtil.isEmpty(materialList)) {
            return;
        }

        Map<String,Object> totalMap = new HashMap<>();
        totalMap.put("totalCtns",0);     // 汇总的数量
        totalMap.put("totalGw",0);       // 汇总的毛重
        totalMap.put("totalAmount",0);   // 汇总的金额

        List<String> totalAmountFormulaList = new ArrayList<>();
        List<String> totalCtnsFormulaList = new ArrayList<>();

        writer1.passRows(writer1CurrIndex);     // 先跳过一定的行，再进行写入
        materialList.stream().forEach(map->{

            String supplierName = String.valueOf(map.get("supplierName"));          // 取出供应商名称
            List<Object> supplierDataList = (List<Object>)map.get("dataList");      // 取出该供应商的物料列表

            int startIndex = writer1CurrIndex;      // 开始的索引

            for(Object obj:supplierDataList) {    // 遍历列表并输出

                ArrayList<Object> objects = (ArrayList<Object>)obj;       // 取出数据

                String no = ObjectUtil.isNotEmpty(objects.get(0))?String.valueOf(objects.get(0)):null;                // 序号
                String itemNo = ObjectUtil.isNotEmpty(objects.get(2))?String.valueOf(objects.get(2)):null;            // itemNo
                String description = ObjectUtil.isNotEmpty(objects.get(3))?String.valueOf(objects.get(3)):null;            // 描述，可能带逗号，我们只需要取逗号前的内容
                if(StrUtil.isNotBlank(description)) {
                    description = description.split("[,|，]")[0];    // 截取逗号前的内容
                }
                String color = ObjectUtil.isNotEmpty(objects.get(4))?String.valueOf(objects.get(4)):null;            // 颜色
                String material = ObjectUtil.isNotEmpty(objects.get(5))?String.valueOf(objects.get(5)):null;      // material
                String itemSize = ObjectUtil.isNotEmpty(objects.get(6))?String.valueOf(objects.get(6)):null;      // itemSize
                int totalQty = 0;
                if(ObjectUtil.isNotEmpty(objects.get(18))) {
                    try {
                        totalQty = Integer.valueOf(String.valueOf(objects.get(18)));      // 总数量
                    } catch (Exception e) {
                        //e.printStackTrace();
                    }
                }
                double unitPrice = 0d;
                if(ObjectUtil.isNotEmpty(objects.get(19))) {
                    try {
                        unitPrice = Double.valueOf(String.valueOf(objects.get(19)));     // 单价
                    } catch (Exception e) {
                        //e.printStackTrace();
                    }
                }
                String amountFormula = "G" + (writer1CurrIndex+1) + "*H" + (writer1CurrIndex+1) ;     // 总价的公式

                // 毛重，可能为空
                double ttlGw = 0;
                if(ObjectUtil.isNotEmpty(objects.get(17))) {
                    try {
                        ttlGw = Double.valueOf(String.valueOf(objects.get(17)));       // 毛总
                    }catch (Exception e) {
                        //e.printStackTrace();
                    }
                }

                // 计算毛重、计算总数量、计算金额汇总，并增加到 totalMap 中去
                totalMap.put("totalCtns",Integer.valueOf(String.valueOf(totalMap.get("totalCtns"))) + totalQty);
                totalMap.put("totalGw",Double.valueOf(String.valueOf(totalMap.get("totalGw"))) + ttlGw);
                totalMap.put("totalAmount",Double.valueOf(String.valueOf(totalMap.get("totalAmount"))) + (totalQty * unitPrice));

                List<Object> rowData = CollUtil.newArrayList(no, itemNo, description, color,material,itemSize,totalQty,unitPrice,amountFormula);
                writeRowDataForOutput1(writer1,rowData,writer1CurrIndex);
                writer1CurrIndex++;
            }

            int endIndex = writer1CurrIndex;    // 结束的索引
            // 写入汇总行
            String ttlQtyFormula = "SUM(G" + (startIndex + 1) + ":G" + endIndex + ")";          // 小计总数量公式
            String ttlAmountFormula = "SUM(I" + (startIndex + 1) + ":I" + endIndex + ")";       // 小计总金额公式
            writeTotal(writer1,supplierName,headInfoVO.getContainerCount(),ttlQtyFormula,ttlAmountFormula);

            totalCtnsFormulaList.add("G" + (endIndex+2));
            totalAmountFormulaList.add("I" + (endIndex+2));

        });

        /**
         * 取出前面的汇总的情况
         */
        Integer totalCtns = Integer.valueOf(String.valueOf(totalMap.get("totalCtns")));    // 总数量
        double totalGw = Double.valueOf(String.valueOf(totalMap.get("totalGw")));          // 总毛重
        double  totalAmount= Double.valueOf(String.valueOf(totalMap.get("totalAmount")));  // 总金额

        /**
         * 开始写入底部的内容
         */
        Font bottomFont = new FontBuilder(writer1).setFontHeight(10).setFontName("Times New Roman").build();

        List<String> blankRow = CollUtil.newArrayList("","","","","","","","","");
        writerBlankRow(writer1,blankRow,writer1CurrIndex,"0");     // 写入空行，并设置无边框
        writer1CurrIndex++;

        // 设置重量相关的内容
        List<Object> ctnsRow = CollUtil.newArrayList("","Ctns:",totalCtns,"","",headInfoVO.getTransportInfo(),"","$",totalAmount);
        writer1.writeRow(ctnsRow);
        /**
         * 设置样式
         */
        Row currRow = writer1.getSheet().getRow(writer1CurrIndex);
        for(int i=0;i<ctnsRow.size();i++) {
            Cell currCell = currRow.getCell(i);
            if(currCell==null) {
                currCell = currRow.createCell(i);
            }
            if(i==1 || i==5) {
                currCell.setCellStyle(new CellStyleBuilder(writer1).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(bottomFont).setCellBorder("12").setDataFormat().build());
            } else if(i == 2){    // Ctns
                currCell.setCellFormula(totalCtnsFormulaList.stream().collect(Collectors.joining(" + ")));
                currCell.setCellStyle(new CellStyleBuilder(writer1).setFillBackgroundColor(1).setHorizontalAlignment(HorizontalAlignment.LEFT).setFillForegroundColor(1).setFont(bottomFont).setCellBorder("14").build());
                writer1Eval.evaluateFormulaCell(currCell);
            }else if(i==8) {       // 总金额
                currCell.setCellFormula(totalAmountFormulaList.stream().collect(Collectors.joining(" + ")));
                currCell.setCellStyle(new CellStyleBuilder(writer1).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(bottomFont).setCellBorder("14").setDataFormat().build());
                writer1Eval.evaluateFormulaCell(currCell);
            } else if(i==6 || i==7) {
                currCell.setCellStyle(new CellStyleBuilder(writer1).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(bottomFont).setCellBorder("1").build());
            } else {
                currCell.setCellStyle(new CellStyleBuilder(writer1).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(bottomFont).setCellBorder("0").build());
            }
        }
        writer1CurrIndex++;

        List<Object> crossWeight = CollUtil.newArrayList("","Gross Weight/KG:",totalGw,"","","Transport/Douane","","$",0);
        writer1.writeRow(crossWeight);
        currRow = writer1.getSheet().getRow(writer1CurrIndex);
        for(int i=0;i<crossWeight.size();i++) {
            Cell currCell = currRow.getCell(i);
            if(i==1) {
                currCell.setCellStyle(new CellStyleBuilder(writer1).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(bottomFont).setCellBorder("23").build());
            } else if(i == 2) {
                currCell.setCellStyle(new CellStyleBuilder(writer1).setFillBackgroundColor(1).setHorizontalAlignment(HorizontalAlignment.LEFT).setFillForegroundColor(1).setFont(bottomFont).setDataFormat().setCellBorder("34").build());
            } else if(i==5) {
                currCell.setCellStyle(new CellStyleBuilder(writer1).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(bottomFont).setCellBorder("2").build());
            } else if(i==8) {
                currCell.setCellStyle(new CellStyleBuilder(writer1).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(bottomFont).setCellBorder("4").setDataFormat().build());
            } else {
                currCell.setCellStyle(new CellStyleBuilder(writer1).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(bottomFont).setCellBorder("0").build());
            }
        }
        writer1CurrIndex++;

        /**
         * 每个集装箱200
         */
        List<Object> loadingCharge = CollUtil.newArrayList("","","","","","loadingCharge","","$",headInfoVO.getContainerCount() * 200);
        writer1.writeRow(loadingCharge);
        currRow = writer1.getSheet().getRow(writer1CurrIndex);
        for(int i=0;i<loadingCharge.size();i++) {
            Cell currCell = currRow.getCell(i);
            if(i==5) {
                currCell.setCellStyle(new CellStyleBuilder(writer1).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(bottomFont).setCellBorder("2").build());
            } else if(i==8) {
                currCell.setCellStyle(new CellStyleBuilder(writer1).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(bottomFont).setCellBorder("4").setDataFormat().build());
            } else {
                currCell.setCellStyle(new CellStyleBuilder(writer1).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(bottomFont).setCellBorder("0").build());
            }
        }
        writer1CurrIndex++;

        /**
         * Commission 10%的（手续费/佣金）
         * 特殊出口港：DISCOVER 港口 7%
         */
        List<Object> commission = new ArrayList<>();
        String shipTo = headInfoVO.getShipTo();         // 出港口
        String commissionDesc = "Commission 10%";
        String commissionRate = "0.1";
        if(StrUtil.isNotBlank(shipTo) && StrUtil.containsAny(shipTo,"discover")) {
            commissionDesc = "Commission 7%";
            commissionRate = "0.07";
        }
        commission = CollUtil.newArrayList("","","","","",commissionDesc,"","$",0);
        writer1.writeRow(commission);
        currRow = writer1.getSheet().getRow(writer1CurrIndex);
        for(int i=0;i<commission.size();i++) {
            Cell currCell = currRow.getCell(i);
            if(currCell==null) {
                currCell = currRow.createCell(i);
            }
            if(i==5) {
                currCell.setCellStyle(new CellStyleBuilder(writer1).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(bottomFont).setCellBorder("2").build());
            } else if(i==8) {        // Commission 10%的（手续费/佣金），特殊出口港：DISCOVER 港口 7%
                currCell.setCellFormula("I" + (writer1CurrIndex-2) + "*" + commissionRate);     //
                currCell.setCellStyle(new CellStyleBuilder(writer1).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(bottomFont).setCellBorder("4").setDataFormat().build());
                writer1Eval.evaluateFormulaCell(currCell);
            } else {
                currCell.setCellStyle(new CellStyleBuilder(writer1).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(bottomFont).setCellBorder("0").build());
            }
        }
        writer1CurrIndex++;

        /**
         * Insurance 保险
         */
        List<Object> insurance = CollUtil.newArrayList("","","","","","Insurance","","$",0);
        writer1.writeRow(insurance);
        currRow = writer1.getSheet().getRow(writer1CurrIndex);
        for(int i=0;i<insurance.size();i++) {
            Cell currCell = currRow.getCell(i);
            if(currCell==null) {
                currCell = currRow.createCell(i);
            }
            if(i==5) {
                currCell.setCellStyle(new CellStyleBuilder(writer1).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(bottomFont).setCellBorder("2").build());
            } else if(i==8) {
                currCell.setCellFormula("Max(I" + (writer1CurrIndex-3) + "*0.0015,23)");
                currCell.setCellStyle(new CellStyleBuilder(writer1).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(bottomFont).setCellBorder("4").setDataFormat().build());
                writer1Eval.evaluateFormulaCell(currCell);
            } else {
                currCell.setCellStyle(new CellStyleBuilder(writer1).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(bottomFont).setCellBorder("0").build());
            }
        }
        writer1CurrIndex++;

        /**
         * COC Report
         */
        List<Object> cocReport = CollUtil.newArrayList("","","","","","COC Report","","$",0);
        writer1.writeRow(cocReport);
        currRow = writer1.getSheet().getRow(writer1CurrIndex);
        for(int i=0;i<insurance.size();i++) {
            Cell currCell = currRow.getCell(i);
            if(i==5) {
                currCell.setCellStyle(new CellStyleBuilder(writer1).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(bottomFont).setCellBorder("2").build());
            } else if(i==8) {
                currCell.setCellStyle(new CellStyleBuilder(writer1).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(bottomFont).setCellBorder("4").setDataFormat().build());
            } else {
                currCell.setCellStyle(new CellStyleBuilder(writer1).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(bottomFont).setCellBorder("0").build());
            }
        }
        writer1CurrIndex++;

        /**
         * ARTDECO TOTAL 汇总
         */
        // List<Object> artdecoTotal = CollUtil.newArrayList("","","","","","ARTDECO TOTAL","","$",0);
        List<Object> artdecoTotal = CollUtil.newArrayList("","","","","",headInfoVO.getSupplier() + " TOTAL","","$",0);
        writer1.writeRow(artdecoTotal);
        currRow = writer1.getSheet().getRow(writer1CurrIndex);
        if(headInfoVO.getContainerCount()>1) {
            currRow.setHeightInPoints(15 * headInfoVO.getContainerCount());
        }
        for(int i=0;i<artdecoTotal.size();i++) {
            Cell currCell = currRow.getCell(i);
            if(currCell==null) {
                currRow.createCell(i);
            }
            if(i==5) {
                currCell.setCellStyle(new CellStyleBuilder(writer1).setVerticalAlignment(VerticalAlignment.CENTER).setFillBackgroundColor(1).setFillForegroundColor(1).setWrapText(true).setFont(new FontBuilder(writer1).setFontName("Times New Roman").setFontHeight(10).setBold(true).build()).setCellBorder("123").build());
            } else if(i==6 || i==7) {
                currCell.setCellStyle(new CellStyleBuilder(writer1).setVerticalAlignment(VerticalAlignment.CENTER).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(bottomFont).setCellBorder("13").build());
            } else if(i==8) {
                currCell.setCellFormula("sum(I" + (writer1CurrIndex-5) + ":I" + writer1CurrIndex + ")");
                currCell.setCellStyle(new CellStyleBuilder(writer1).setVerticalAlignment(VerticalAlignment.CENTER).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(bottomFont).setCellBorder("134").setDataFormat().build());
                writer1Eval.evaluateFormulaCell(currCell);   // 强制执行公式
            } else {
                currCell.setCellStyle(new CellStyleBuilder(writer1).setVerticalAlignment(VerticalAlignment.CENTER).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(bottomFont).setCellBorder("0").build());
            }
        }
        writer1CurrIndex++;

    }




    /**
     * 写入总数
     * @param writer1
     * @param supplierName
     * @param containerCount   集装箱数量
     * @param ttlQtyFormula
     * @param ttlAmountFormula
     */
    private void writeTotal(ExcelWriter writer1, String supplierName,int containerCount, String ttlQtyFormula, String ttlAmountFormula) {

        List<String> blankRow = CollUtil.newArrayList("","","","","","","","","");


        writerBlankRow(writer1,blankRow,writer1CurrIndex,"1");     // 写入空行，并设置上边框
        writer1CurrIndex++;

        List<Object> totalRowData = CollUtil.newArrayList(null,null,null,null,null,supplierName + " Total QTY",ttlQtyFormula,null,ttlAmountFormula);
        writer1.writeRow(totalRowData);       // 写入汇总数量

        // 设置格式
        Row totalRow = writer1.getSheet().getRow(writer1CurrIndex);
        totalRow.setHeightInPoints(30);
        Font font = new FontBuilder(writer1).setFontName("Arial").setFontHeight(9).build();
        for(int i=0;i<totalRowData.size();i++) {
            Cell currCell = totalRow.getCell(i);
            if(currCell==null) {
                totalRow.createCell(i);
            }
            if(i==5) {
                currCell.setCellStyle(new CellStyleBuilder(writer1).setCellBorder("123").setWrapText(true).setHorizontalAlignment(HorizontalAlignment.CENTER).setVerticalAlignment(VerticalAlignment.CENTER).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(font).build());    // 上左下边框
            } else if(i == 6) {      // 商品总数量
                currCell.setCellFormula(ttlQtyFormula);
                currCell.setCellStyle(new CellStyleBuilder(writer1).setCellBorder("134").setHorizontalAlignment(HorizontalAlignment.CENTER).setVerticalAlignment(VerticalAlignment.CENTER).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(font).build());    // 上下右边框
                writer1Eval.evaluateFormulaCell(currCell);
            } else if(i == totalRowData.size()-1) {    // 小计金额
                currCell.setCellFormula(ttlAmountFormula);
                currCell.setCellStyle(new CellStyleBuilder(writer1).setCellBorder("1234").setHorizontalAlignment(HorizontalAlignment.CENTER).setVerticalAlignment(VerticalAlignment.CENTER).setDataFormat2().setFont(font).build());    // 四边都加边框
                writer1Eval.evaluateFormulaCell(currCell);
            } else {
                currCell.setCellStyle(new CellStyleBuilder(writer1).setCellBorder("0").setHorizontalAlignment(HorizontalAlignment.CENTER).setVerticalAlignment(VerticalAlignment.CENTER).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(font).build());    // 无边框
            }
        }

        writer1CurrIndex++;

        // 再写入空行
        writerBlankRow(writer1,blankRow,writer1CurrIndex,"0");     // 写入空行，不设置下边框
        writer1CurrIndex++;

    }

    /**
     * 写入数据行
     * @param writer
     * @param rowData
     */
    private void writeRowDataForOutput2(ExcelWriter writer, List<Object> rowData,int currIndex) {

        writer.writeRow(rowData);      // 写入数据行
        // 设置格式
        Row currRow = writer.getSheet().getRow(currIndex);
        currRow.setHeightInPoints(50);
        Font font = new FontBuilder(writer).setFontName("Arial").setFontHeight(9).build();
        for(int i=0; i< rowData.size(); i++) {
            Cell currCell = currRow.getCell(i);     // 取出当前单元格式
            if(currCell==null) {
                currCell = currRow.createCell(i);
            }
            if(i==0) {    // 第一列时
                currCell.setCellStyle(new CellStyleBuilder(writer).setHorizontalAlignment(HorizontalAlignment.CENTER).setVerticalAlignment(VerticalAlignment.CENTER).setWrapText(true).setFont(font).setCellBorder(22,0,22,22).build());
            } else if(i == rowData.size() -1) {  // 最后一列时
                currCell.setCellStyle(new CellStyleBuilder(writer).setHorizontalAlignment(HorizontalAlignment.CENTER).setVerticalAlignment(VerticalAlignment.CENTER).setWrapText(true).setDataFormat(2).setFont(font).setCellBorder(22,22,22,0).build());
            } else if(i==9){
                currCell.setCellStyle(new CellStyleBuilder(writer).setHorizontalAlignment(HorizontalAlignment.CENTER).setVerticalAlignment(VerticalAlignment.CENTER).setWrapText(true).setDataFormat(3).setFont(font).setCellBorder(22,22,22,22).build());
            }else if(i==10){
                currCell.setCellStyle(new CellStyleBuilder(writer).setHorizontalAlignment(HorizontalAlignment.CENTER).setVerticalAlignment(VerticalAlignment.CENTER).setWrapText(true).setDataFormat(2).setFont(font).setCellBorder(22,22,22,22).build());
            } else {
                currCell.setCellStyle(new CellStyleBuilder(writer).setHorizontalAlignment(HorizontalAlignment.CENTER).setVerticalAlignment(VerticalAlignment.CENTER).setWrapText(true).setFont(font).setCellBorder(22,22,22,22).build());
            }
        }
    }


    /**
     * 写入数据行
     * @param writer
     * @param rowData
     */
    private void writeRowDataForOutput1(ExcelWriter writer, List<Object> rowData,int currIndex) {

        writer.writeRow(rowData);      // 写入数据行
        // 设置格式
        Row currRow = writer.getSheet().getRow(currIndex);
        currRow.setHeightInPoints(25);
        Font font = new FontBuilder(writer).setFontName("Arial").setFontHeight(9).build();
        for(int i=0; i< rowData.size(); i++) {
            Cell currCell = currRow.getCell(i);     // 取出当前单元格式
            if(currCell==null) {
                currCell = currRow.createCell(i);
            }
            if(i==0) {    // 第一列时
                currCell.setCellStyle(new CellStyleBuilder(writer).setHorizontalAlignment(HorizontalAlignment.CENTER).setVerticalAlignment(VerticalAlignment.CENTER).setWrapText(true).setFont(font).setCellBorder(22,0,22,22).build());
            } else if (i == rowData.size() -2) {  // 倒数第二列时，单价，需要加入$符号
                currCell.setCellStyle(new CellStyleBuilder(writer).setHorizontalAlignment(HorizontalAlignment.CENTER).setVerticalAlignment(VerticalAlignment.CENTER).setWrapText(true).setFont(font).setDataFormat2().setCellBorder(22,22,22,22).build());
            } else if(i == rowData.size() -1) {  // 最后一列时
                currCell.setCellFormula(String.valueOf(rowData.get(i)));
                currCell.setCellStyle(new CellStyleBuilder(writer).setHorizontalAlignment(HorizontalAlignment.CENTER).setVerticalAlignment(VerticalAlignment.CENTER).setWrapText(true).setFont(font).setDataFormat2().setCellBorder(22,22,22,0).build());
                writer1Eval.evaluateFormulaCell(currCell);
            } else {
                currCell.setCellStyle(new CellStyleBuilder(writer).setHorizontalAlignment(HorizontalAlignment.CENTER).setVerticalAlignment(VerticalAlignment.CENTER).setWrapText(true).setFont(font).setCellBorder(22,22,22,22).build());
            }
        }
    }

    /**
     * 根据传入的数据列表，解析出表头信息
     * @return
     */
    @Override
    public HeadInfoVO getHeadInfoByDataList() {


        HeadInfoVO headInfoVO = null;
        if(CollectionUtil.isNotEmpty(dataList)) {
            try {
                String telInfo = getContentFromDataList(dataList,1,20);
                String address = getContentFromDataList(dataList,2,20);
                String date = getContentFromDataList(dataList,4,20);    // 这里获取的日期格式可能是：Date:2023.06.02
                if(StrUtil.isNotBlank(date)) {    // 将 "Date:" 或是 "Date：" 清除掉
                    date = StrUtil.replace(date,"Date:","");
                    date = StrUtil.replace(date,"Date：","");
                }
                String piNo = getContentFromDataList(dataList,5,20);     // 这里取出来的，格式可能是：PI No.: PT230516-N-7G  或是 PI No.: PT230516-N-7G
                if(StrUtil.isNotBlank(piNo)) {
                    String[] piNoSplit = piNo.split("[:|：]");
                    if(piNoSplit.length>1) {
                        piNo = "INV No.: " + piNoSplit[1];     // 重新拼接
                    }
                }
                /**
                 * 形式发票收款人：
                 *      Proforma Invoice to :
                 *          ORCA DECO GAB
                 *          LIKOUALA  SAINT GERMAIN( EN FACE DE KAMELIE)
                 *          BP 1540 / LIBREVILLE GABON
                 *          TELEPHONE : +24102764444
                 */
                String proforma = getContentFromDataList(dataList, 6, 0);
                if(StrUtil.isNotBlank(proforma)) {
                    proforma = proforma.replace("Proforma Invoice to :","");
                }

                /**
                 * 装运港信息
                 *          Port of Loading: Shanghai China
                 *              Ship to: GABON
                 *              Payment: 20% deposit, 80% against B/L copy.
                 *              Transport:   1*40HQ by sea shipment
                 *              Delivery Date:  in the middle of July, 2023
                 */
                String partLoading = getContentFromDataList(dataList,6,20);
                /**
                 * 得到的map有两个结果：
                 *      port -> Shanghai China
                 *      type -> 1*40HQ
                 */
                Map<String,String> portAndTypeMap = getPortAndTypeMapByPartLoading(partLoading);
                String port = portAndTypeMap.get("port");
                String type = portAndTypeMap.get("type");
                String shipTo = portAndTypeMap.get("shipTo");    // 出港口

                /**
                 * 根据 type 的值，解析到底有多少个集装箱
                 * 1*40HQ 或是 X
                 */
                int containerCount = 0;
                if(StrUtil.isNotBlank(type)) {
                    try {
                        containerCount = Integer.valueOf(type.split("[*|X|x]")[0]);
                    } catch (Exception e) {
                        //e.printStackTrace();
                    }
                }

                /**
                 * 读取运输港口, 用于在 Unit Price / Amount 下使用
                 */
                String transportInfo = getContentFromDataList(dataList,8,20);

                String supplier = getSupplierFromDataList(dataList);

                /**
                 * 实例化
                 */
                headInfoVO = new HeadInfoVO();

                headInfoVO.setTelInfo(telInfo);
                headInfoVO.setAddress(address);
                headInfoVO.setSupplier(supplier);
                headInfoVO.setDate(date);
                headInfoVO.setPort(port);
                headInfoVO.setType(type);
                headInfoVO.setContainerCount(containerCount);
                headInfoVO.setProformaInvoiceTo(proforma);
                headInfoVO.setTransportInfo(transportInfo);
                headInfoVO.setInvNo(piNo);
                headInfoVO.setShipTo(shipTo);   // 出港口

            } catch (Exception e) {
                e.printStackTrace();
            }

        }

        return headInfoVO;
    }

    /**
     * 输入表头
     * @param headInfoVO
     */
    private void outputHead(HeadInfoVO headInfoVO) {

        if(ObjectUtil.isEmpty(headInfoVO)) {
            return;
        }

        /**
         * 一、输出生成1的表头
         */
        // 输出电话与地址
        CellStyle telAndAddressStyle = new CellStyleBuilder(writer1).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(new FontBuilder(writer1).setFontHeight(8).setColor(21).build()).build();
        writeContent(writer1,1,0,headInfoVO.getTelInfo(),telAndAddressStyle);
        writeContent(writer1,2,0,headInfoVO.getAddress(),telAndAddressStyle);

        // 输出供应商、日期、Port、Type
        writeContent(writer1,4,1,headInfoVO.getSupplier(),new CellStyleBuilder(writer1).setVerticalAlignment(VerticalAlignment.CENTER).setCellBorder("14").setWrapText(true).setFont(new FontBuilder(writer1).setFontName("Times New Roman").setFontHeight(9).setBold(true).build()).build());   // 供应商,自动换行
        writeContent(writer1,5,1,headInfoVO.getDate(),new CellStyleBuilder(writer1).setVerticalAlignment(VerticalAlignment.CENTER).setFillBackgroundColor(1).setFillForegroundColor(1).setCellBorder("4").setFont(new FontBuilder(writer1).setFontName("Times New Roman").setFontHeight(9).setBold(true).build()).build());
        writeContent(writer1,6,1,headInfoVO.getPort(),new CellStyleBuilder(writer1).setVerticalAlignment(VerticalAlignment.CENTER).setFillBackgroundColor(1).setFillForegroundColor(1).setCellBorder("4").setFont(new FontBuilder(writer1).setFontName("Times New Roman").setFontHeight(9).setBold(true).build()).build());
        writeContent(writer1,7,1,headInfoVO.getType(),new CellStyleBuilder(writer1).setVerticalAlignment(VerticalAlignment.CENTER).setCellBorder("34").setFont(new FontBuilder(writer1).setFontName("Times New Roman").setFontHeight(9).setBold(true).build()).build());
        // proforma
        writeContent(writer1,2,6,headInfoVO.getProformaInvoiceTo(),new CellStyleBuilder(writer1).setVerticalAlignment(VerticalAlignment.TOP).setWrapText(true).setCellBorder("12").setFont(new FontBuilder(writer1).setFontName("Arial").setFontHeight(9).setBold(true).build()).build());

        // INV NO
        writeContent(writer1,8,8,headInfoVO.getInvNo(),new CellStyleBuilder(writer1).setHorizontalAlignment(HorizontalAlignment.RIGHT).setFont(new FontBuilder(writer1).setFontName("Arial").setFontHeight(9).setBold(true).build()).build());

        /**
         * 设置标题栏的样式
         */
        Row titleRow = writer1.getSheet().getRow(9);     // 取出第10行
        Font titleFont = new FontBuilder(writer1).setBold(true).setFontName("Arial").setFontHeight(10).build();
        for(int i=0; i < 9; i++) {
            Cell currCell = titleRow.getCell(i);      // 取出单元格
            if(currCell == null) {
                currCell = titleRow.createCell(i);
            }
            if(i==0) {     // 第一列
                currCell.setCellStyle(new CellStyleBuilder(writer1).setFillForegroundColor(22).setVerticalAlignment(VerticalAlignment.CENTER).setHorizontalAlignment(HorizontalAlignment.CENTER).setCellBorder(0,0,0,1).setFont(titleFont).build());
            } else if(i==8) {    // 最后一列
                currCell.setCellStyle(new CellStyleBuilder(writer1).setFillForegroundColor(22).setVerticalAlignment(VerticalAlignment.CENTER).setHorizontalAlignment(HorizontalAlignment.CENTER).setCellBorder(0,1,1,0).setFont(titleFont).build());
            } else if(i==7) {    // 倒数第二列
                currCell.setCellStyle(new CellStyleBuilder(writer1).setFillForegroundColor(22).setVerticalAlignment(VerticalAlignment.CENTER).setHorizontalAlignment(HorizontalAlignment.CENTER).setCellBorder(0,1,1,1).setFont(titleFont).setWrapText(true).build());
            } else {
                currCell.setCellStyle(new CellStyleBuilder(writer1).setFillForegroundColor(22).setVerticalAlignment(VerticalAlignment.CENTER).setHorizontalAlignment(HorizontalAlignment.CENTER).setCellBorder(0,1,0,1).setFont(titleFont).setWrapText(true).build());
            }

        }


        // 写入运输港口
        writeContent(writer1,10,7,headInfoVO.getTransportInfo(),new CellStyleBuilder(writer1).setFillForegroundColor(22).setCellBorder("3").setHorizontalAlignment(HorizontalAlignment.CENTER).setVerticalAlignment(VerticalAlignment.CENTER).setFont(new FontBuilder(writer1).setFontHeight(9).setBold(true).build()).build());

        // 填写集装箱信息
        for (int i=0;i < headInfoVO.getContainerCount();i++) {
            if(headInfoVO.getContainerCount() == 1) {    // 只有一个集装箱时，不加编号
                writer1.merge(writer1CurrIndex,writer1CurrIndex,0,8,"CONTAINER NO.&SEAL NO.: ",new CellStyleBuilder(writer1).setHorizontalAlignment(HorizontalAlignment.LEFT).setCellBorder("234").setFont(new FontBuilder(writer1).setFontName("Arial").setBold(true).setFontHeight(10).build()).build());
            } else {
                writer1.merge(writer1CurrIndex,writer1CurrIndex,0,8,"CONTAINER " + (i+1) + " NO.&SEAL NO.: ",new CellStyleBuilder(writer1).setHorizontalAlignment(HorizontalAlignment.LEFT).setCellBorder("234").setFont(new FontBuilder(writer1).setFontName("Arial").setBold(true).setFontHeight(10).build()).build());
            }
            writer1CurrIndex++;     // 行数索引增加1
        }

        /**
         * 二、写入生成2的表头
         */
        CellStyle telAndAddressStyleForWriter2 = new CellStyleBuilder(writer2).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(new FontBuilder(writer2).setFontName("Times New Roman").setFontHeight(8).setBold(true).setColor(21).build()).build();
        writeContent(writer2,1,0,headInfoVO.getTelInfo(),telAndAddressStyleForWriter2);
        writeContent(writer2,2,0,headInfoVO.getAddress(),telAndAddressStyleForWriter2);

        // 输出供应商、日期、Port、Type
        writeContent(writer2,4,1,headInfoVO.getSupplier(),new CellStyleBuilder(writer2).setVerticalAlignment(VerticalAlignment.CENTER).setCellBorder("14").setWrapText(true).setFont(new FontBuilder(writer2).setFontName("Times New Roman").setFontHeight(9).setBold(true).build()).build());   // 供应商, 自动换行
        writeContent(writer2,5,1,headInfoVO.getDate(),new CellStyleBuilder(writer2).setVerticalAlignment(VerticalAlignment.CENTER).setFillBackgroundColor(1).setFillForegroundColor(1).setCellBorder("4").setFont(new FontBuilder(writer2).setFontName("Times New Roman").setFontHeight(9).setBold(true).build()).build());
        writeContent(writer2,6,1,headInfoVO.getPort(),new CellStyleBuilder(writer2).setVerticalAlignment(VerticalAlignment.CENTER).setFillBackgroundColor(1).setFillForegroundColor(1).setCellBorder("4").setFont(new FontBuilder(writer2).setFontName("Times New Roman").setFontHeight(9).setBold(true).build()).build());
        writeContent(writer2,7,1,headInfoVO.getType(),new CellStyleBuilder(writer2).setVerticalAlignment(VerticalAlignment.CENTER).setCellBorder("34").setFont(new FontBuilder(writer2).setFontName("Times New Roman").setFontHeight(9).setBold(true).build()).build());
        // proforma
        writeContent(writer2,2,7,headInfoVO.getProformaInvoiceTo(),new CellStyleBuilder(writer2).setVerticalAlignment(VerticalAlignment.TOP).setWrapText(true).setCellBorder("12").setFont(new FontBuilder(writer2).setFontName("Arial").setFontHeight(9).build()).build());
        // INV NO
        writeContent(writer2,8,11,headInfoVO.getInvNo(),new CellStyleBuilder(writer2).setHorizontalAlignment(HorizontalAlignment.RIGHT).setFont(new FontBuilder(writer2).setFontName("Arial").setFontHeight(9).setBold(true).build()).build());

        /**
         * 设置标题栏的样式
         */
        Row titleRow2 = writer2.getSheet().getRow(9);     // 取出第10行
        Font titleFont2 = new FontBuilder(writer2).setBold(true).setFontName("Arial").setFontHeight(10).build();
        for(int i=0; i < 12; i++) {
            Cell currCell = titleRow2.getCell(i);      // 取出单元格
            if(currCell == null) {
                currCell = titleRow2.createCell(i);
            }
            if(i==0) {     // 第一列
                currCell.setCellStyle(new CellStyleBuilder(writer2).setFillForegroundColor(22).setVerticalAlignment(VerticalAlignment.CENTER).setHorizontalAlignment(HorizontalAlignment.CENTER).setCellBorder(0,0,0,1).setFont(titleFont2).build());
            } else if(i==11) {    // 最后一列
                currCell.setCellStyle(new CellStyleBuilder(writer2).setFillForegroundColor(22).setVerticalAlignment(VerticalAlignment.CENTER).setHorizontalAlignment(HorizontalAlignment.CENTER).setCellBorder(0,1,0,0).setFont(titleFont2).setWrapText(true).build());
            } else {
                currCell.setCellStyle(new CellStyleBuilder(writer2).setFillForegroundColor(22).setVerticalAlignment(VerticalAlignment.CENTER).setHorizontalAlignment(HorizontalAlignment.CENTER).setCellBorder(0,1,0,1).setFont(titleFont2).setWrapText(true).build());
            }

        }

        // 填写集装箱信息
        for (int i=0;i < headInfoVO.getContainerCount();i++) {
            if(headInfoVO.getContainerCount()==1) {    // 只有一个集装箱时，不加编号
                writer2.merge(writer2CurrIndex,writer2CurrIndex,0,11,"CONTAINER NO.&SEAL NO.: ",new CellStyleBuilder(writer2).setHorizontalAlignment(HorizontalAlignment.LEFT).setVerticalAlignment(VerticalAlignment.CENTER).setCellBorder("234").setFont(new FontBuilder(writer2).setFontName("Arial").setBold(true).setFontHeight(10).build()).build());
            } else {
                writer2.merge(writer2CurrIndex,writer2CurrIndex,0,11,"CONTAINER " + (i + 1) + " NO.&SEAL NO.: ",new CellStyleBuilder(writer2).setHorizontalAlignment(HorizontalAlignment.LEFT).setVerticalAlignment(VerticalAlignment.CENTER).setCellBorder("234").setFont(new FontBuilder(writer2).setFontName("Arial").setBold(true).setFontHeight(10).build()).build());
            }
            writer2CurrIndex++;     // 行数索引增加1
        }
    }

}
